from flask import Flask, render_template, jsonify, request
from flask_cors import CORS
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address
from app.services.organization.organization_service import organization_service
from app.core.config import settings
from app.infrastructure import db_pool, api_client
from app.utils.log_control import INFO, ERROR
from app.config.password_config import verify_password
from sqlalchemy import text
import urllib3
import os
import json
from app.utils.monitoring import SystemMonitor
import logging
from werkzeug.exceptions import HTTPException
from app.web.routes import register_routes, api

# 禁用SSL警告
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)

# 禁用Flask默认的访问日志
log = logging.getLogger('werkzeug')
log.setLevel(logging.ERROR)  # 只显示错误级别的日志

# 定义更新SQL映射
UPDATE_SQL_MAPPING = {
    'name': "UPDATE per_main SET name = :value WHERE id = :id",
    'level': "UPDATE per_main SET level = :value WHERE id = :id",
    'gender': "UPDATE per_main SET gender = :value WHERE id = :id",
    'department': "UPDATE per_main SET per_code = :value WHERE id = :id",
    'position': "UPDATE per_main SET manage_name = :value WHERE id = :id",
    'entry_date': "UPDATE per_main SET join_work_date = :value WHERE id = :id",
    'status': "UPDATE per_main SET status = :value WHERE id = :id"
}

# 获取当前文件所在目录
current_dir = os.path.dirname(os.path.abspath(__file__))

# 创建Flask应用实例
app = Flask(__name__,
           static_folder='static',  # 相对于当前目录的static文件夹
           template_folder='templates')  # 相对于当前目录的templates文件夹

# 启用CORS
CORS(app)

# 设置调试模式
app.config.from_object(settings)

# 创建限流器实例
limiter = Limiter(
    app=app,
    key_func=get_remote_address,  # 使用IP地址作为限制键
    default_limits=["200 per day", "50 per hour"],  # 默认限制
    storage_uri="memory://"  # 使用内存存储
)

# 创建全局实例
api_client = api_client
organization_service = organization_service

# 创建系统监控实例
system_monitor = SystemMonitor()

@app.route('/')
def index():
    """渲染主页"""
    return render_template('index.html')

@app.route('/test')
def test_page():
    """渲染测试页面"""
    return render_template('test.html')

@app.route('/api/token')
def get_token():
    """获取token的API接口"""
    try:
        if api_client.token:
            return jsonify({
                'success': True,
                'token': api_client.token
            })
        return jsonify({
            'success': False,
            'error': '获取token失败，请检查API服务是否可用'
        }), 500
    except Exception as e:
        ERROR.logger.error(f"处理token请求时发生错误: {str(e)}")
        return jsonify({
            'success': False,
            'error': f'系统错误: {str(e)}'
        }), 500


@app.route('/api/comparison/total')
def get_total_comparison():
    """获取总人数对比数据"""
    try:
        result = organization_service.get_total_comparison()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取总人数对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/comparison/rank')
def get_rank_comparison():
    """获取职级分布对比数据"""
    try:
        result = organization_service.get_rank_comparison()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取职级分布对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/db/data')
def get_db_data():
    """获取数据库表数据"""
    try:
        result = organization_service.get_db_data()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取数据库表数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/api-data')
def get_api_data():
    """获取API数据"""
    try:
        result = organization_service.get_api_data()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取API数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/comparison/age')
def get_age_comparison():
    """获取平均年龄对比数据"""
    try:
        result = organization_service.get_average_age_comparison()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取平均年龄对比数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/comparison/match-rate')
def get_match_rate():
    """获取匹配率数据"""
    try:
        result = organization_service.get_match_rate()
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取匹配率数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/comparison/gender-ratio/api')
def get_gender_ratio_api():
    """获取API的性别比例数据"""
    try:
        org_id = request.args.get('org_id', '')
        result = organization_service.get_gender_ratio_api(org_id)
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取API性别比例数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/comparison/gender-ratio/db')
def get_gender_ratio_db():
    """获取数据库的性别比例数据，使用SQL查询实现"""
    try:
        org_id = request.args.get('org_id', '')
        
        # 构建SQL查询
        sql_query = """
        select so.id as belongId, so.name as orgName, 
        concat('1:', round(if(female = 0, 0, female / male), 2)) as textValue
        from (
            SELECT so.id as belongId, so.name,
            sum(CASE WHEN gender = '1' THEN 1 ELSE 0 END) as male, 
            sum(CASE WHEN gender = '2' THEN 1 ELSE 0 END) as female
            from sys_org so
            LEFT JOIN(
                SELECT REPLACE(b.path_code,"-",",") pathCode, pm.gender
                FROM per_main pm
                JOIN sys_org b ON pm.org_code = b.CODE
            ) temp0 on FIND_IN_SET(so.`code`,temp0.pathCode)
            WHERE 1=1
        """
        
        # 如果有组织ID参数，添加筛选条件
        if org_id and org_id.strip():
            sql_query += f" AND so.id = {org_id}"
        
        # 完成SQL查询
        sql_query += " GROUP BY so.id, so.name) t"
        
        # 执行SQL查询
        result = organization_service.execute_raw_sql(sql_query)
        
        # 检查响应格式
        if isinstance(result, dict) and 'data' in result:
            return jsonify(result)
        else:
            # 转换为标准格式
            return jsonify({
                "success": True,
                "data": result
            })
    except Exception as e:
        ERROR.logger.error(f"获取数据库性别比例数据失败: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500


@app.route('/api/comparison/gender-ratio')
def get_gender_ratio_combined():
    """获取性别比例对比数据（同时包含API和数据库数据）"""
    try:
        org_id = request.args.get('org_id', '')
        
        # 分别获取API和数据库的性别比例数据
        api_result = organization_service.get_gender_ratio_api(org_id)
        
        # 构建SQL查询
        sql_query = """
        select so.id as belongId, so.name as orgName, 
        concat('1:', round(if(female = 0, 0, female / male), 2)) as textValue
        from (
            SELECT so.id as belongId, so.name,
            sum(CASE WHEN gender = '1' THEN 1 ELSE 0 END) as male, 
            sum(CASE WHEN gender = '2' THEN 1 ELSE 0 END) as female
            from sys_org so
            LEFT JOIN(
                SELECT REPLACE(b.path_code,"-",",") pathCode, pm.gender
                FROM per_main pm
                JOIN sys_org b ON pm.org_code = b.CODE
            ) temp0 on FIND_IN_SET(so.`code`,temp0.pathCode)
            WHERE 1=1
        """
        
        # 如果有组织ID参数，添加筛选条件
        if org_id and org_id.strip():
            sql_query += f" AND so.id = {org_id}"
        
        # 完成SQL查询
        sql_query += " GROUP BY so.id, so.name) t"
        
        # 执行SQL查询
        db_result = organization_service.execute_raw_sql(sql_query)
        
        # 如果db_result不是dict或没有data字段，进行转换
        if not isinstance(db_result, dict) or 'data' not in db_result:
            db_result = {
                "success": True,
                "data": db_result
            }
        
        # 合并两个结果，标记来源
        combined_data = []
        
        # 处理API数据
        if api_result and api_result.get('success', False) and api_result.get('data'):
            for item in api_result['data']:
                item['source'] = 'api'
                combined_data.append(item)
        
        # 处理数据库数据
        if db_result and db_result.get('success', False) and db_result.get('data'):
            for item in db_result['data']:
                item['source'] = 'db'
                combined_data.append(item)
        
        # 返回合并后的结果
        return jsonify({
            "success": True,
            "data": combined_data
        })
    except Exception as e:
        ERROR.logger.error(f"获取性别比例对比数据失败: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500


@app.route('/api/db/update', methods=['POST'])
def update_db_data():
    """更新数据库记录"""
    try:
        if not request.is_json:
            return jsonify({"error": "Content-Type must be application/json"}), 400
            
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON data provided"}), 400

        if not all(k in data for k in ('id', 'field', 'value')):
            return jsonify({"error": "Missing required fields"}), 400
            
        row_id = data.get('id')
        field = data.get('field')
        value = data.get('value')
        
        # 验证字段名
        allowed_fields = {'name', 'level', 'gender', 'department', 'position', 'entry_date', 'status'}
        if field not in allowed_fields:
            return jsonify({"error": f"Invalid field name '{field}'. Allowed fields are: {', '.join(allowed_fields)}"}), 400
        
        # 从映射中获取对应的SQL语句
        sql_str = UPDATE_SQL_MAPPING.get(field)
        if not sql_str:
            return jsonify({"error": f"No SQL mapping found for field: {field}"}), 400
        
        # 执行更新
        try:
            with db_pool.get_db() as db:
                # 将SQL字符串转换为SQLAlchemy text对象
                sql = text(sql_str)
                result = db.execute(sql, {'value': value, 'id': row_id})
                
                if result == 0:  # 没有记录被更新
                    return jsonify({"error": f"No record found with id: {row_id}"}), 404
                    
                return jsonify({
                    "success": True,
                    "message": "Record updated successfully"
                })
                
        except Exception as db_error:
            ERROR.logger.error(f"数据库更新失败: {str(db_error)}")
            return jsonify({"error": "Database update failed"}), 500
            
    except Exception as e:
        ERROR.logger.error(f"更新数据失败: {str(e)}")
        return jsonify({"error": "Internal server error"}), 500


@app.route('/api/db/add', methods=['POST'])
def add_db_data():
    try:
        data = request.get_json()

        # 字段映射
        field_mapping = {
            'department': 'per_code',
            'position': 'manage_name',
            'entry_date': 'join_work_date'
        }
        
        # 处理字段映射
        mapped_data = {}
        for key, value in data.items():
            # 跳过 ID 字段，让数据库自动处理
            if key.lower() != 'id':
                mapped_key = field_mapping.get(key, key)
                mapped_data[mapped_key] = value
        
        # 添加必要的默认字段
        mapped_data['del_flag'] = 0  # 0表示未删除
        
        # 检查是否已存在相同记录
        check_sql = text("""
            SELECT COUNT(*) as count 
            FROM per_main 
            WHERE name = :name 
            AND level = :level 
            AND per_code = :per_code 
            AND del_flag = 0
        """)
        
        with db_pool.get_db() as db:
            # 先检查是否存在
            check_result = db.execute_query(check_sql, {
                'name': mapped_data.get('name'),
                'level': mapped_data.get('level'),
                'per_code': mapped_data.get('per_code')
            })
            
            if check_result['data'][0]['count'] > 0:
                return jsonify({
                    "error": "已存在相同的记录",
                    "success": False
                }), 400
            
            # 构建插入SQL，使用命名参数
            fields = ', '.join(mapped_data.keys())
            placeholders = ', '.join([f':{key}' for key in mapped_data.keys()])
            insert_sql = f"INSERT INTO per_main ({fields}) VALUES ({placeholders})"
            
            # 执行插入
            result = db.execute(insert_sql, mapped_data)
            if result > 0:
                return jsonify({"success": True})
            else:
                return jsonify({"error": "插入失败"}), 500
            
    except Exception as e:
        ERROR.logger.error(f"添加数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/db/delete', methods=['POST'])
def delete_db_data():
    try:
        data = request.get_json()
        row_id = data.get('id')
        
        # 执行删除，使用命名参数
        with db_pool.get_db() as db:
            sql = text("DELETE FROM per_main WHERE id = :id")
            db.execute(sql, {'id': row_id})
            return jsonify({"success": True})
            
    except Exception as e:
        ERROR.logger.error(f"删除数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/personnel/api')
def get_api_personnel():
    """获取API人员数据"""
    try:
        series = request.args.get('series')
        level = request.args.get('level')
        
        if not series or not level:
            return jsonify({"error": "Missing required parameters"}), 400
            
        result = organization_service.get_api_personnel_by_level(series, level)
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取API人员数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/personnel/db')
def get_db_personnel():
    """获取数据库人员数据"""
    try:
        series = request.args.get('series')
        level = request.args.get('level')
        
        if not series or not level:
            return jsonify({"error": "Missing required parameters"}), 400
            
        result = organization_service.get_db_personnel_by_level(series, level)
        return jsonify(result)
    except Exception as e:
        ERROR.logger.error(f"获取数据库人员数据失败: {str(e)}")
        return jsonify({"error": str(e)}), 500


@app.route('/api/login', methods=['POST'])
@limiter.limit("3 per minute")  # 每分钟最多3次登录尝试
def login():
    """处理登录请求"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({
                'success': False,
                'error': '无效的请求数据'
            }), 400

        username = data.get('username')
        password = data.get('password')

        if not username or not password:
            return jsonify({
                'success': False,
                'error': '用户名和密码不能为空'
            }), 400

        # 使用api_client进行登录
        if api_client._login():
            return jsonify({
                'success': True,
                'token': api_client.token
            })
        else:
            return jsonify({
                'success': False,
                'error': '登录失败'
            }), 401

    except Exception as e:
        ERROR.logger.error(f"登录过程中发生错误: {str(e)}")
        return jsonify({
            'success': False,
            'error': f'系统错误: {str(e)}'
        }), 500


@app.route('/api/verify_password', methods=['POST'])
@limiter.limit("5 per minute")  # 每分钟最多5次尝试
def verify_admin_password():
    """验证管理员密码"""
    try:
        if not request.is_json:
            return jsonify({
                'success': False,
                'error': '无效的请求格式'
            }), 400
            
        data = request.get_json()
        password = data.get('password')
        
        if not password:
            return jsonify({
                'success': False,
                'error': '密码不能为空'
            }), 400
            
        # 获取客户端IP
        ip_address = request.remote_addr
        
        # 验证密码
        is_valid, error_message = verify_password(password, ip_address)
        
        if is_valid:
            return jsonify({'success': True})
        else:
            return jsonify({
                'success': False,
                'error': error_message
            }), 401
            
    except Exception as e:
        ERROR.logger.error(f"密码验证失败: {str(e)}")
        return jsonify({
            'success': False,
            'error': f'系统错误: {str(e)}'
        }), 500


@app.route('/api/monitoring/metrics', methods=['GET'])
@limiter.exempt  # 豁免此路由的频率限制，因为需要频繁刷新监控数据
def get_monitoring_metrics():
    """获取系统监控指标"""
    try:
        # 获取系统指标
        metrics = system_monitor.collect_metrics()
        
        # 获取请求统计
        response_data = {
            'system': {
                'cpu': metrics.get('cpu', 0),
                'memory': metrics.get('memory', 0),
                'disk': metrics.get('disk', 0)
            }
        }
        
        return jsonify(response_data), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 添加错误处理器
@app.errorhandler(429)
def ratelimit_handler(e):
    """处理请求超限的情况"""
    return jsonify({
        'success': False,
        'error': f'请求过于频繁，请稍后再试。{str(e.description)}'
    }), 429


# 导入并调用register_routes函数注册路由
register_routes(app)
# 注册蓝图
app.register_blueprint(api, url_prefix='/api')


if __name__ == '__main__':
    app.run(debug=True)
